USE master;
GO

IF DB_ID('CustomersDB') IS NOT NULL DROP DATABASE [CustomersDB];
GO

CREATE DATABASE [CustomersDB];
GO

USE [CustomersDB];
GO

CREATE SCHEMA Sales AUTHORIZATION dbo;
GO

CREATE TABLE Sales.[Customer](customer_id INT           NOT NULL IDENTITY(1,1) 
                                CONSTRAINT PK_Customer PRIMARY KEY,
                              name        VARCHAR(100) NOT NULL,
                              address     VARCHAR(200) NOT NULL);
                       
CREATE TABLE Sales.[Order](order_id      INT  NOT NULL IDENTITY(1,1) 
                            CONSTRAINT PK_Order PRIMARY KEY,
                           number        INT  NOT NULL,
                           order_date    DATE NOT NULL 
                            CONSTRAINT DFT_order_date DEFAULT(CURRENT_TIMESTAMP),
                           amount        INT  NOT NULL,
                           description   VARCHAR(800),
                           customer_id   INT  NOT NULL);

ALTER TABLE Sales.[Order] ADD FOREIGN KEY (customer_id) REFERENCES Sales.[Customer](customer_id);

SET IDENTITY_INSERT Sales.[Customer] ON;
GO

INSERT INTO Sales.[Customer](customer_id, name, address) VALUES 
            (1, 'Slot', 'Minsk, Umanskaya str, 24'),
            (2, 'Photoshop', 'Minsk, Nemiga str, 3'),
            (3, 'Vip', 'Minsk, Nemiga str., 8'),
            (4, '21vek', 'Minsk, Timiryazeva str., 65A'),
            (5, 'Sity', 'Minsk, Fariciusa str., 8b'),
            (6, 'Foto Land', 'Minsk, Rafieva str., 113'),
            (7, 'Electro Market', 'Minsk, Curganova str., 57A');
            
SET IDENTITY_INSERT Sales.[Customer] OFF;
GO

INSERT INTO Sales.[Order](number, order_date, amount, description, customer_id) VALUES 
            (1055,'2012-10-10', 7282, 'Payment by VISA', 1),
            (4878,'2011-08-12', 6712, 'Payment by MasterCard', 4),
            (5543,'2010-01-03', 7692, 'Payment by Webmoney', 2),
            (8450,'2008-12-15', 8085, 'Payment by cash', 3),
            (9932,'2012-11-25', 2395, 'Payment by MasterCard', 7),
            (8402,'2011-11-13', 6948, 'Payment by cash', 5),
            (4957,'2011-09-01', 8018, 'Payment by VISA', 6),
            (8726,'2012-07-18', 1846, 'Payment by MasterCard', 1),
            (7353,'2012-05-14', 7156, 'Payment by MasterCard', 1),
            (8364,'2011-12-06', 9658, 'Payment by cash', 5),
            (5420,'2009-08-01', 9482, 'Payment by VISA', 7),
            (7403,'2009-05-15', 7294, 'Payment by MasterCard', 4),
            (6186,'2012-04-11', 7579, 'Payment by cash', 6),
            (1770,'2012-03-24', 4105, 'Payment by cash', 2),
            (2870,'2012-09-30', 1132, 'Payment by MasterCard', 4),
            (8739,'2011-05-24', 3615, 'Payment by VISA', 5),
            (5051,'2009-10-20', 9290, 'Payment by MasterCard', 3),
            (8350,'2011-02-10', 9300, 'Payment by cash', 6),
            (2461,'2012-04-14', 6013, 'Payment by MasterCard', 5),
            (4003,'2008-06-13', 6436, 'Payment by VISA', 2),
            (3356,'2007-07-13', 1677, 'Payment by MasterCard', 7),
            (1639,'2012-11-16', 3659, 'Payment by MasterCard', 4),
            (7036,'2011-12-02', 8439, 'Payment by VISA', 5);    
GO

CREATE PROCEDURE [dbo].[OrdersByDescription]
(@desc VARCHAR(800))
AS
SELECT order_id,number,amount
FROM Sales.[Order]
WHERE [description]=@desc;
